<?php
// $Id$

/**
 * Controller_Install 控制器
 * 安装ent的
 */
class Controller_Install extends Controller_Abstract
{

    function actionIndex()
	{
        // 为 $this->_view 指定的值将会传递数据到视图中
		# $this->_view['text'] = 'Hello!';
        
        
        header("Content-type: text/html; charset=utf-8");

        $ret = "";

		if(isset($_GET['user'])){ 
        	//重新创建user表
        	$ret .= $this->actionUser();
        }
        //重新创建sec表
        $ret .= $this->actionSec();

        //重新创建ent表
        $ret .= $this->actionEnt();

        //重新创建mkt表和填充数据
        $ret .= $this->actionMarket();
        
        //重新创建sec_comment表
        $ret .= $this->actionSecComment();
        
        //重新创建sec_comment_sum表
        $ret .= $this->actionSecCommentSum();
        
        //重新创建sec_follow表
        $ret .= $this->actionSecFollow();
        
        //create news table
        $ret .= $this->actionPress();
        
        //create news_relation table
        $ret .= $this->actionSecPressRelation();
        
        //create comment of press table;
        $ret .= $this->actionPressComment();

        //开始向数据库中导入数据
        //假定每个证券市场的证券的编号不超过1万，选择这些证券的后四位数就能唯一标识出这个证券市场中的证券。比如600001， 这里的1 唯一标示出上海证券交易所的一个唯一的证券。
        //这样做是为了保证每次入库的时候，每个证券的id不变

        $dataPath = array(
            array("ent" => "/xampp/htdocs/spider/60ent.txt", "sec" => "/xampp/htdocs/spider/60sec.txt", "offset"=> 600000),
            array("ent" => "/xampp/htdocs/spider/00ent.txt", "sec" => "/xampp/htdocs/spider/00sec.txt", "offset"=> 0)
        );
        $sql = "set character_set_database = gbk";
        DB::query($sql);

        foreach($dataPath as $p){
            $sql = "load data local infile '{$p['ent']}' into table ent (name, address, tel, fax, site, email, cate, intro)";
            DB::query($sql);

            $ret .= "安装ent表成功，数据从{$p['ent']} load<br />";

            $secfile = $p['sec'];

            $f = file($secfile);
            if($f == null){
                $ret .= "打开文件错误，文件路径： $secfile <br />";
                return $ret;
            }
            $sql = "set character_set_client = gbk";
            DB::query($sql);

            foreach($f as $line){
                $line = split("\t", $line);
                $entName = trim($line[4]);
                $mktId = intval(trim($line[0]));
                $code = trim($line[1]);
                $name = trim($line[2]);
                $type = trim($line[3]);
                $seq = substr($code, 2);
                $i = $p['offset'] + intval($seq);
                $sql = "insert into sec set id = '$i', mkt_id = '$mktId', name = '$name', code = '$code', type = '$type', ent_id = (select id from ent where name = '$entName')";
                DB::query($sql);
                $i++;
            }

            $ret .= "安装sec表成功，数据来自{$p['sec']} <br />";
        }
        
        //重新创建sec_alias表
        $ret .= $this->actionSecAlias();
        
        $ret .= $this->actionSecSum();
        
        return $ret;

	}

    /*
     * 创建market表，向market表中导入数据，相当于marketTable和marketData
     */
    function actionMarket(){
        try{
            $ret = "";

            $sql = "drop table if exists mkt";
            DB::query($sql);

            $sql = "create table `mkt`(
                `id` int unsigned not null auto_increment primary key,
                `en_name` varchar(4) not null,
                `zh_name` varchar(4) not null,
                `fullname` varchar(10) not null
                )engine = myisam default charset = utf8";
            DB::query($sql);
            $ret .= "安装mkt表成功 <br />";
            
            $sql = "insert into mkt(id, en_name, zh_name, fullname) values
                ('21', 'SHA', '上证', '上海证券交易所'),
                ('31', 'SHE', '深证', '深证证券交易所'),
                ('34', 'STB', '三板', '深圳三板市场');
            ";
            DB::query($sql);
            $ret .= "向数据库中添加mkt数据成功 <br />";
        }catch(Exception $e){
            $ret .= "操作market表失败，错误原因：".$e."<br />";
        }

            return $ret;

    }

    function errorMsg($str){
        return "<span style='color:red'>$str</span>";
    }

    /**
      * 重新创建Sec表
      */
    function actionSec(){
        try{
            $sql = "drop table if exists sec";
            DB::query($sql);

            $sql = " CREATE TABLE `sec` (
                `id` int(10) unsigned NOT NULL auto_increment,
                `mkt_id` int(10) unsigned NOT NULL,
                `name` varchar(8) NOT NULL,
                `code` varchar(15) NOT NULL,
                `type` enum('stock','warrant','debt','fund') default 'stock',
                `ent_id` int(10) unsigned default NULL,
                PRIMARY KEY  (`id`)
              ) ENGINE=MyISAM  DEFAULT CHARSET=utf8";
            DB::query($sql);
            return "安装sec数据库成功 <br />";
        }catch(Exception $e){
            return $this->errorMsg("创建sec数据库错误，错误原因:".$e."<br />");
        }

    }

    /**
      *重新创建Ent表
      */
    function actionEnt(){
        try{
            $sql = "drop table if exists ent";
            DB::query($sql);
                                
            $sql = "CREATE TABLE `ent` (
              `id` int(10) unsigned NOT NULL auto_increment,
              `name` varchar(100) NOT NULL,
              `address` varchar(100) NOT NULL,
              `tel` varchar(15) NOT NULL,
              `fax` varchar(15) NOT NULL,
              `site` varchar(100) NOT NULL,
              `email` varchar(100) NOT NULL,
              `cate` varchar(20) NOT NULL,
              `intro` text,
              PRIMARY KEY  (`id`)
            ) ENGINE=MyISAM  DEFAULT CHARSET=utf8";
            DB::query($sql);
            return "安装ent数据库成功 <br />";
        }catch(Exception $e){
            return $this->errorMsg("创建ent数据库错误，错误原因:".$e."<br />");
        }
    }
    
    /*
     * 创建用户user表
     */
    function actionUser(){
        try{
            $ret = "";
            $sql = "drop table if exists user";
            DB::query($sql);

            $sql = "create table user(
                id int unsigned not null auto_increment primary key,
                name varchar(24) not null,
                nickname varchar(24) not null,
                email varchar(40) not null,
                password varchar(40) not null,
                actived varchar(32) not null,
                image_id int unsigned default '1',  /* 1为用户默认头像 */
                title varchar(24) not null,
                subtitle varchar(120) default '',
                step int unsigned not null default 0,
                register_time date not null
            )default charset = utf8 engine = myisam";
            DB::query($sql);
            $passwd = sha1('!@#$#$%%%^TYTYHJNHJ');
            $sql = "insert into user values(1, 'witpn', 'witpn', 'ikombat@gmail.com', 
                '$passwd', '1', 1, 'witpn的页面', '', 0, now())";
            DB::query($sql);
            
            $ret .= "安装user表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return $this->errorMsg("创建user数据表错误，错误原因: ".$e."<br />");
        }
    }
    
    function actionPress(){
        try{
            $ret = "";
            $sql = "drop table if exists sec_press";
            DB::query($sql);

            $sql = "create table sec_press(
                id int unsigned not null auto_increment primary key,
                title varchar(120) default '',
                press varchar(240) not null,
                press_time date not null,
                link varchar(240) default '',
                tags varchar(48) default '',
                view varchar(240) default '',
              	view_time datetime not null,
              	src varchar(24) default '',
              	user_id int unsigned not null
            )default charset = utf8 engine = myisam";
            DB::query($sql);
            $ret .= "安装sec_press表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return $this->errorMsg("创建sec_press数据表错误，错误原因: ".$e."<br />");
        }
    }
    
    function actionPressComment(){
    	try{
            $ret = "";
            $sql = "drop table if exists press_comment";
            DB::query($sql);

            $sql = "create table press_comment(
                id int unsigned not null auto_increment primary key,
                comment varchar(1200) not null,
                press_id int unsigned not null,
                comment_time datetime not null,
                user_id int unsigned not null
            )default charset = utf8 engine = myisam";
            DB::query($sql);
            $ret .= "安装press_comment表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return $this->errorMsg("创建press_comment数据表错误，错误原因: ".$e."<br />");
        }
    }
    
    
    function actionSecPressRelation(){
        try{
            $ret = "";
            $sql = "drop table if exists sec_press_relation";
            DB::query($sql);

            $sql = "create table sec_press_relation(
              	sec_id int unsigned not null,
              	press_id int unsigned not null,
              	primary key(sec_id, press_id)
            )default charset = utf8 engine = innodb";
            DB::query($sql);
            $ret .= "安装sec_press_relation表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return $this->errorMsg("创建sec_press_relation数据表错误，错误原因: ".$e."<br />");
        }
    }
    
    
    function actionSecSum(){
    	try{
            $ret = "";
            $sql = "drop table if exists sec_sum";
            DB::query($sql);

            $sql = "create table sec_sum(
            	id int unsigned not null primary key,
                follow_cnt int unsigned not null,
                view_cnt int unsigned not null,
                press_cnt int unsigned not null
            )default charset = utf8 engine = innodb";
            DB::query($sql);
            
            $sql = "insert into sec_sum select id, 0, 0, 0 from sec";
            DB::query($sql);
            
            $ret .= "安装sec_sum表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return $this->errorMsg("创建sec_sum数据表错误，错误原因: ".$e."<br />");
        }
    }
    
    /*
     * 创建sec_alias表
     */
    function actionSecAlias(){
        try{
            //这个表描述证券的别名：证券的名称，证券的代码，证券的别名，甚至证券的前两位 都被用作证券的名称
            $ret = "";
            $sql = "drop table if exists sec_alias";
            DB::query($sql);

            $sql = "create table sec_alias(
                sec_id int unsigned not null,
                alias char(8)
            ) default charset = utf8 engine = myisam;";
            DB::query($sql);
            
            $sql = "insert into sec_alias(sec_id, alias) select id, code from sec";
            DB::query($sql);
            
            $sql = "insert into sec_alias(sec_id, alias) select id, name from sec";
            DB::query($sql);
            
            $sql = "create index sec_alias_id on sec_alias(sec_id)";
            DB::query($sql);
            
            $ret .= "安装sec_alias表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return errMsg("创建sec_alias表错误，错误原因: ".$e."<br />");
        }
    }

    /*
     * 创建sec_comment表
     */
    function actionSecComment(){
        try{
            $ret = "";
            $sql = "drop table if exists sec_comment";
            DB::query($sql);
            
            $sql = "create table sec_comment(
              id int unsigned not null auto_increment primary key,
              comment text,
              user_id int unsigned not null,
              sec_id int unsigned not null,
              comment_time datetime,
              vote tinyint check(-1, 0, 1)
            )default charset=utf8;";
            DB::query($sql);

            $sql = "create index comment_time_index on sec_comment(comment_time);";
            DB::query($sql);

            $ret .= "安装sec_comment表 和 comment_time_index索引 成功 <br />";
            return $ret;
        }catch(Exception $e){
            return errMsg("创建sec_comment表错误，错误原因: ".$e."<br />");
        }
    }

    /*
     * 创建sec_comment_sum表
     */
    function actionSecCommentSum(){
        try{
            $ret = "";
            $sql = "drop table if exists sec_comment_sum";
            DB::query($sql);
            
            $sql = "create table sec_comment_sum(
               sec_id int unsigned not null,
               date date not null,
               comment_cnt int default 0, /* vote = 0的数量 */
               up_cnt int  default 0, /* vote = 1 的数量 */
               down_cnt int default 0, /* vote = -1的数量 */
               comment_id int unsigned not null,
               last_comment_time datetime,
               primary key(date, sec_id)
            )default charset = utf8;";
            DB::query($sql);
            $ret .= "安装sec_comment_sum表成功 <br />";
            return $ret;
        }catch(Exception $e){
            return errMsg("创建sec_comment_sum表错误，错误原因: ".$e."<br />");
        }
    }
    

    /*
     * 创建sec_follow表
     */
    function actionSecFollow(){
        try{
            $ret = "";
            $sql = "drop table if exists sec_follow";
            DB::query($sql);
            
            $sql = "create table sec_follow(
              user_id int unsigned not null,
              sec_id int unsigned not null,
              follow_time datetime not null,
              primary key(user_id, sec_id)
            )default charset=utf8;";
            DB::query($sql);
                
            $sql = "alter table sec_follow add index sec_follow_sec_id(sec_id);";
            DB::query($sql);
            
            $sql = "alter table sec_follow add index sec_follow_follow_time(follow_time);";
            DB::query($sql);

            $ret .= "安装sec_follow表 和 sec_follow_sec_id、 sec_follow_follow_time索引 成功 <br />";
            return $ret;
        }catch(Exception $e){
            return errMsg("创建sec_follow表错误，错误原因: ".$e."<br />");
        }
    }
}

?>
